const DB = require('../utils/useMysql')

/**
 * 获取所有书签
 * @param {number} page   页码
 * @param {number} limit  条数
 * @param {number} isLogin 是否登录
 * @returns {Promise}
 */
exports.getBooksSql = async (page = 1, limit = 100, isLogin = false) => {
  const total_sql = isLogin
    ? `SELECT COUNT(*) as total FROM books WHERE is_show = 1`
    : `SELECT COUNT(*) as total FROM books WHERE is_show = 1 AND status = 1`

  const count = await DB.exec({
    sql: total_sql,
    params: []
  })

  // 计算总数
  const total = count[0]['total']

  if (total === 0) {
    return { result: [], total }
  }

  const books_sql = isLogin
    ? `SELECT 
  book.book_id, book_url, book_name, book_desc, books.books_id, books.books_desc, books.books_name, books.status as books_status, book.status as book_status
  FROM books 
  INNER JOIN book ON books.books_id = book.books_id 
  WHERE books.is_show = 1 AND book.is_show = 1
  ORDER BY book.book_id DESC
  LIMIT ? OFFSET ?;`
    : `SELECT 
  book.book_id, book_url, book_name, book_desc, books.books_id, books.books_desc, books.books_name,  books.status as books_status, book.status as book_status
  FROM books 
  INNER JOIN book ON books.books_id = book.books_id 
  WHERE books.is_show = 1 AND book.is_show = 1 AND books.status = 1 AND book.status = 1
  ORDER BY book.book_id DESC
  LIMIT ? OFFSET ?;`

  const result = await DB.exec({
    sql: books_sql,
    params: [limit, page - 1]
  })

  return { result, total }
}

/**
 * 添加书签
 * @param {string} books_id      书签id
 * @param {string} book_url      书签链接
 * @param {string} book_name     书签名称
 * @param {string} book_desc     书签描述
 * @param {string} books_id      书签id
 * @returns
 */
exports.addBooksSql = (book_id, book_url, book_name, book_desc, books_id, status = 1) => {
  return DB.exec({
    sql: `INSERT INTO book (book_id, book_url, book_name, book_desc, books_id, status, is_show)
    VALUES  (?, ?, ?, ?, ?, ?, ?,1)`,
    params: [book_id, book_url, book_name, book_desc, books_id, status]
  })
}

/**
 * 修改书签
 * @param {string} book_id       书签id
 * @param {string} book_url      书签链接
 * @param {string} book_name     书签名称
 * @param {string} book_desc     书签描述
 * @param {string} books_id      书签id
 * @param {number} status         状态
 * @returns
 */
exports.updateBooksSql = (book_id, book_url, book_name, book_desc, books_id, status) => {
  return DB.exec({
    sql: `UPDATE book SET book_url = ?, book_name = ?, book_desc = ?, books_id = ?, status = ? WHERE book_id = ? AND is_show = 1`,
    params: [book_url, book_name, book_desc, books_id, status, book_id]
  })
}

/**
 * 删除书签
 * @param {string} book_id       书签id
 * @returns
 */
exports.deleteBooksSql = (book_id) => {
  return DB.exec({
    sql: `UPDATE book SET is_show = 0 WHERE book_id = ?`,
    params: [book_id]
  })
}

// /**
//  * 获取书签列表
//  * @param {string} books_id      书签id
//  * @param {number} page          页码
//  * @param {number} limit         每页条数
//  * @returns
//  */
// exports.getBooksListSql = async (books_id, page = 1, limit = 15) => {
//   const count = await DB.exec({
//     sql: `SELECT COUNT(*) as total FROM books WHERE is_show = 1`,
//     params: []
//   })

//   const total = count[0]['total']

//   if (total === 0) {
//     return { result: [], total }
//   }

//   const result = books_id
//     ? await DB.exec({
//         sql: `SELECT book_id, book_url, book_name, book_desc, books.books_id, books.books_desc, books.books_name
//         FROM book
//         INNER JOIN books ON book.books_id = books.books_id
//         WHERE books.books_id = ? ORDER BY books_id DESC LIMIT ? OFFSET ?;`,
//         params: [books_id, limit, page - 1]
//       })
//     : await DB.exec({
//         sql: `SELECT books_id, books_name, books_desc FROM books WHERE is_show = 1 ORDER BY books_id DESC LIMIT ? OFFSET ?;`,
//         params: [limit, page - 1]
//       })

//   return { result, total }
// }
